﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

namespace TestJQGrid
{
    public partial class WebForm2 : CommanUI
    {
        #region Global declarations
        
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                fill_fields();
                fill_Funs();
                fill_dlFormula();
                fun_disableControl();

                lblWhenExp.Text = "";
                lblThenExp.Text = "";
                tableNames.Value = "";
                btnSave.Visible = false;
                ViewState["formulaIDToUpdate"] = 0;

                lblMenuName.Text = "FORMULA";
                lblMenuName.Font.Bold = true;
                lblMenuName.Font.Size = 14;
                lblMenuName.ForeColor = System.Drawing.Color.Navy;
            }

         //   btnReset.Attributes.Add("OnClick", "document.forms[0].reset();return false;");
        }

        public void fun_disableControl()
        {
            parm_1.Enabled = false;
            parm_2.Enabled = false;
            parm_3.Enabled = false;
            parm_4.Enabled = false;
            parm_5.Enabled = false;

            ddReturnType_1.Enabled = false;
            ddReturnType_2.Enabled = false;
            ddReturnType_3.Enabled = false;
            ddReturnType_4.Enabled = false;
            ddReturnType_5.Enabled = false;
        }

        private void fill_fields()
        {
            DataLayer ddl = new DataLayer();
            listFields.DataSource = ddl.getDataSource("select (cp.TableName + '.' + cp.ColumnName) AS ColumnsList,('@('+cp.TableName + '.' + cp.ColumnName+')') AS ColumnsListVal  from ColumnProperties cp where cp.TableName in ( select tr.RelatedTable from TableRelation tr, ProductList pl where pl.ProductName = '" + Session["ProductName"].ToString() + "' AND pl.TableName = tr.PrimaryTable ) OR cp.TableName in ( select pl.TableName from ProductList pl where pl.ProductName = '" + Session["ProductName"].ToString() + "') AND cp.ColumnName not in ('EntryStatus','Product','TransId','ProductName') Order By ColumnsList");
            listFields.DataTextField = "ColumnsList";
            listFields.DataValueField = "ColumnsListVal";
            listFields.DataBind();

            listFields.Width = 200;
        }

        private void fill_Funs()
        {
            DataLayer ddl = new DataLayer();
            listFormula.DataSource = ddl.getDataSource("SELECT Value FROM ValidValues WHERE (TableName = 'Formula')");
            listFormula.DataTextField = "Value";
            listFormula.DataValueField = "Value";
            listFormula.DataBind();

            listFormula.Width = 100;
        }

        private void fill_dlFormula()
        {
            DataLayer ddl = new DataLayer();
            dlFormula.DataSource = ddl.getDataSource("select FormulaName,ID from FormulaTable where FormulaType='Formula'");
            dlFormula.DataBind();
        }

        protected void btnValidate_Click(object sender, EventArgs e)
        {
            //if (validateForm() == true)
            //{
            string result = "";
            string when_expResult = "", when_expResultType = "";
            string then_expResult = "", then_expResultType = "";
            string when_expression = txtWhenPart.Text.Trim().Replace(" ", "");
            string then_expression = txtThenPart.Text.Trim().Replace(" ", "");

                if (when_expression.Length > 0)
                {
                    object obj = isValidExp(when_expression);
                    when_expResult = Convert.ToString(obj);
                    when_expResultType = obj.GetType().ToString();
                }

                if (then_expression.Length > 0)
                {
                    object obj = isValidExp(then_expression);
                    then_expResult = Convert.ToString(obj);
                    then_expResultType = obj.GetType().ToString();
                }

                if (when_expResult !="" &&  when_expResult.ToUpper() != "TRUE" && when_expResult.ToUpper() != "FALSE") //when_expResult.IndexOf("Invalid") > -1
                {
                    if (string.IsNullOrEmpty(result))
                    {
                        result = " WHEN expression";
                    }
                    else
                    {
                        result += " and WHEN expression";
                    }

                    lblWhenExp.Text = "Invalid expression";
                    lblWhenExp.ForeColor = System.Drawing.Color.Red;
                }
                else
                {
                    if (when_expResult != "")
                    {
                        lblWhenExp.Text = "Valid expression";
                        lblWhenExp.ForeColor = System.Drawing.Color.Green;
                    }
                }

                if (then_expResult.IndexOf("Invalid") > -1)
                {
                    if (string.IsNullOrEmpty(result))
                    {
                        result = " Then expression";
                    }
                    else
                    {
                        result += " and Then expression";
                    }

                    lblThenExp.Text = "Invalid expression";
                    lblThenExp.ForeColor = System.Drawing.Color.Red;
                }
                else
                {
                    lblThenExp.Text = "Valid expression";
                    lblThenExp.ForeColor = System.Drawing.Color.Green;
                }

                if (result != "")
                {
                    btnSave.Visible = false;
                }
                else
                {
                    btnSave.Visible = true;
                }
           // }            
        }

        private bool validateForm()
        {
            string msg = "";

            if (txtFurmulaName.Text.Trim().Replace(" ", "").Length == 0 || txtFurmulaName.Text == string.Empty)
            {
                msg += "Please enter the formula name.";
            }
            
            if (txtThenPart.Text.Trim().Replace(" ", "").Length == 0 || txtThenPart.Text == string.Empty)
            {
                msg += "Please enter the Then Part.";
            }

            if (msg != "")
            {
                showMessage(msg);
                return false;
            }
            else
            {
                return true;
            }
        }

        protected void cb_1_CheckedChanged(object sender, EventArgs e)
        {
            fun_enableControl(cb_1, parm_1, ddReturnType_1);
        }

        public void fun_enableControl(CheckBox cb,TextBox tb,DropDownList dl)
        {
            if (cb.Checked == true)
            {
                tb.Enabled = true;
                dl.Enabled = true;
            }
            else
            {
                tb.Text = "";
                dl.SelectedValue = "number";

                tb.Enabled = false;
                dl.Enabled = false;
            }
        }

        protected void cb_2_CheckedChanged(object sender, EventArgs e)
        {
            fun_enableControl(cb_2, parm_2, ddReturnType_2);
        }

        protected void cb_4_CheckedChanged(object sender, EventArgs e)
        {
            fun_enableControl(cb_4, parm_4, ddReturnType_4);
        }

        protected void cb_5_CheckedChanged(object sender, EventArgs e)
        {
            fun_enableControl(cb_5, parm_5, ddReturnType_5);
        }

        protected void cb_3_CheckedChanged(object sender, EventArgs e)
        {
            fun_enableControl(cb_3, parm_3, ddReturnType_3);
        }
        /// <summary>
        /// This function save's the formula in the table.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSave_Click(object sender, EventArgs e)
        {
            string strGroup = "";
            string strWhere = "";
            string expQuery = txtThenPart.Text.Trim().Replace(" ", "");
            
            // Group By Query
            if (expQuery.IndexOf("SUM") > -1 || expQuery.IndexOf("MIN") > -1 || expQuery.IndexOf("MAX") > -1 || expQuery.IndexOf("AVG") > -1 || expQuery.IndexOf("COUNT") > -1)
            {
                strGroup = txtThenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");
                strGroup = strGroup.Replace("SUM", "").Replace("MIN", "").Replace("MAX", "").Replace("AVG", "").Replace("COUNT", "");

                strWhere = txtWhenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");
                strWhere = strWhere.Replace("SUM", "").Replace("MIN", "").Replace("MAX", "").Replace("AVG", "").Replace("COUNT", "");

                
                    expQuery = "select " + txtThenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "") + " ";
                    expQuery += " from " + tableNames.Value;
                    if (strWhere != "")
                        expQuery += " where " + strWhere;
                    expQuery += " group by " + strGroup;
                    //expQuery += " having " + txtWhenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");
                
            }
            else // Without GROUP BY clause
            {
                strWhere = txtWhenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");
                strWhere = strWhere.Replace("SUM", "").Replace("MIN", "").Replace("MAX", "").Replace("AVG", "").Replace("COUNT", "");

                
                    expQuery = "select " + txtThenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "") + " ";
                    expQuery += " from " + tableNames.Value;
                    if (strWhere != "")
                        expQuery += " where " + strWhere;
                
            }

            DataLayer ddl = new DataLayer();

            if (Convert.ToInt32(ViewState["formulaIDToUpdate"]) != 0)
            {
                if (ddl.insertQuery("update FormulaTable set FormulaName='" + txtFurmulaName.Text.Trim() + "',WhenPart='" + txtWhenPart.Text.Trim().Replace(" ", "") + "',ThenPart='" + txtThenPart.Text.Trim().Replace(" ", "") + "',ReturnType='" + ddReturnType.SelectedValue.ToString() + "',FinalQuery='" + expQuery.Trim() + "' where ID=" + Convert.ToInt32(ViewState["formulaIDToUpdate"])) > 0)
                {
                    string paraID = "";
                    string sqlParam="";

                    if(cb_1.Checked==true)
                    {
                        sqlParam = "";
                        paraID = getResult(paraID, hf_1.Value);
                        sqlParam = "update FormulaParameters set ParameterName='" + parm_1.Text.Trim() + "',ParameterType='" + ddReturnType_1.SelectedValue + "' where FormulaID=" + Convert.ToInt32(ViewState["formulaIDToUpdate"]) + " and ID=" + hf_1.Value;
                        ddl.insertQuery(sqlParam);
                    }

                    if(cb_2.Checked==true)
                    {
                        sqlParam = "";
                        paraID = getResult(paraID, hf_2.Value);
                        sqlParam = "update FormulaParameters set ParameterName='" + parm_2.Text.Trim() + "',ParameterType='" + ddReturnType_2.SelectedValue + "' where FormulaID=" + Convert.ToInt32(ViewState["formulaIDToUpdate"]) + " and ID=" + hf_2.Value;
                        ddl.insertQuery(sqlParam);
                    }

                    if(cb_3.Checked==true)
                    {
                        sqlParam = "";
                        paraID = getResult(paraID, hf_3.Value);
                        sqlParam = "update FormulaParameters set ParameterName='" + parm_3.Text.Trim() + "',ParameterType='" + ddReturnType_3.SelectedValue + "' where FormulaID=" + Convert.ToInt32(ViewState["formulaIDToUpdate"]) + " and ID=" + hf_3.Value;
                        ddl.insertQuery(sqlParam);
                    }

                    if(cb_4.Checked==true)
                    {
                        sqlParam = "";
                        paraID = getResult(paraID, hf_4.Value);
                        sqlParam = "update FormulaParameters set ParameterName='" + parm_4.Text.Trim() + "',ParameterType='" + ddReturnType_4.SelectedValue + "' where FormulaID=" + Convert.ToInt32(ViewState["formulaIDToUpdate"]) + " and ID=" + hf_4.Value;
                        ddl.insertQuery(sqlParam);
                    }

                    if(cb_5.Checked==true)
                    {
                        sqlParam = "";
                        paraID = getResult(paraID, hf_5.Value);
                        sqlParam = "update FormulaParameters set ParameterName='" + parm_5.Text.Trim() + "',ParameterType='" + ddReturnType_5.SelectedValue + "' where FormulaID=" + Convert.ToInt32(ViewState["formulaIDToUpdate"]) + " and ID=" + hf_5.Value;
                        ddl.insertQuery(sqlParam);
                    }
                    
                    ddl.insertQuery(sqlParam);
                    string qtx = "delete from FormulaParameters where FormulaID=" + Convert.ToInt32(ViewState["formulaIDToUpdate"]) + " and ID not in(" + paraID + ")";
                    ddl.insertQuery(qtx);
                    fill_dlFormula();
                    showMessage("Formula updated successfully.");
                }
            }
            else
            {
                string query = "insert into FormulaTable ( FormulaName, WhenPart, ThenPart, ReturnType, FormulaType, CreatedOn, CreatedBy, FinalQuery, ProductName )" ;
                query += " values('" + txtFurmulaName.Text.Trim().Replace("'", "") + "','" + txtWhenPart.Text.Trim() + "','" + txtThenPart.Text.Trim() + "','" + ddReturnType.SelectedValue.ToString();
                query += "','Formula','" + DateTime.Now.ToString() + "','" + Session["loginId"].ToString() + "','" + expQuery + "','" + Session["ProductName"].ToString() + "') ";

                int fid = GlobalMethods.ExecuteIdentityInsert(query, "FormulaID");

                bool addPara = false;

                if (fid > 0)
                {
                    for (int i = 1; i <= 5; i++)
                    {
                        string pname = "";
                        string ptype = "";

                        switch (i)
                        {
                            case 1:
                                pname = parm_1.Text.Trim();
                                ptype = ddReturnType_1.SelectedValue;
                                if (cb_1.Checked == true)
                                    addPara = true;
                                else
                                    addPara = false;
                                break;
                            case 2:
                                pname = parm_2.Text.Trim();
                                ptype = ddReturnType_2.SelectedValue;
                                if (cb_2.Checked == true)
                                    addPara = true;
                                else
                                    addPara = false;
                                break;
                            case 3:
                                pname = parm_3.Text.Trim();
                                ptype = ddReturnType_3.SelectedValue;
                                if (cb_3.Checked == true)
                                    addPara = true;
                                else
                                    addPara = false;
                                break;
                            case 4:
                                pname = parm_4.Text.Trim();
                                ptype = ddReturnType_4.SelectedValue;
                                if (cb_4.Checked == true)
                                    addPara = true;
                                else
                                    addPara = false;
                                break;
                            case 5:
                                pname = parm_5.Text.Trim();
                                ptype = ddReturnType_5.SelectedValue;
                                if (cb_5.Checked == true)
                                    addPara = true;
                                else
                                    addPara = false;
                                break;
                        }

                        if (addPara)
                            ddl.insertQuery("insert into FormulaParameters values(" + fid + ",'" + pname + "','" + ptype + "')");

                        addPara = false;
                    }

                    fill_dlFormula();
                    showMessage("Formula Saved Successfully");
                }
            }
        }

        private string getResult(string result,string val)
        {
            if (string.IsNullOrEmpty(result))
            {
                result = val;
            }
            else
            {
                result+=","+val;
            }

            return result;
        }

        protected void lbt_formula_Click(object sender, EventArgs e)
        {
            int rid = 0;
            LinkButton lbtn = sender as LinkButton;

            if (lbtn != null)
            {
                rid = Convert.ToInt32(lbtn.CommandArgument);
                showFormulDetails(rid);
                ViewState["formulaIDToUpdate"] = rid;
            }
        }

        private void showFormulDetails(int fid)
        {
            DataLayer ddl = new DataLayer();
            DataTable dt = new DataTable();
            dt = ddl.getDataSource("select * from FormulaTable ft where ft.ID=" + fid);

            if (dt != null && dt.Rows.Count > 0)
            {
                txtFurmulaName.Text = dt.Rows[0]["FormulaName"].ToString();
                ddReturnType.SelectedValue = dt.Rows[0]["ReturnType"].ToString();
                txtWhenPart.Text = dt.Rows[0]["WhenPart"].ToString();
                txtThenPart.Text = dt.Rows[0]["ThenPArt"].ToString();

                DataTable dtp = new DataTable();
                dtp = ddl.getDataSource("select * from FormulaParameters where FormulaID=" + fid);

                if (dtp != null && dtp.Rows.Count > 0)
                {
                    resetForUpdate();

                    for (int i = 0; i < dtp.Rows.Count; i++)
                    {
                        switch (i + 1)
                        {
                            case 1:
                                cb_1.Enabled = true;
                                parm_1.Enabled = true;
                                ddReturnType_1.Enabled = true;
                                
                                cb_1.Checked = true;
                                hf_1.Value = dtp.Rows[i]["ID"].ToString();
                                parm_1.Text = dtp.Rows[i]["ParameterName"].ToString();
                                ddReturnType_1.SelectedValue = dtp.Rows[i]["ParameterType"].ToString();
                                break;
                            case 2:
                                cb_2.Enabled = true;
                                parm_2.Enabled = true;
                                ddReturnType_2.Enabled = true;
                                
                                cb_2.Checked = true;
                                hf_2.Value = dtp.Rows[i]["ID"].ToString();
                                parm_2.Text = dtp.Rows[i]["ParameterName"].ToString();
                                ddReturnType_2.SelectedValue = dtp.Rows[i]["ParameterType"].ToString();
                                break;
                            case 3:
                                cb_3.Enabled = true;
                                parm_3.Enabled = true;
                                ddReturnType_3.Enabled = true;

                                cb_3.Checked = true;
                                hf_3.Value = dtp.Rows[i]["ID"].ToString();
                                parm_3.Text = dtp.Rows[i]["ParameterName"].ToString();
                                ddReturnType_3.SelectedValue = dtp.Rows[i]["ParameterType"].ToString();
                                break;
                            case 4:
                                cb_4.Enabled = true;
                                parm_4.Enabled = true;
                                ddReturnType_4.Enabled = true;

                                cb_4.Checked = true;
                                hf_4.Value = dtp.Rows[i]["ID"].ToString();
                                parm_4.Text = dtp.Rows[i]["ParameterName"].ToString();
                                ddReturnType_4.SelectedValue = dtp.Rows[i]["ParameterType"].ToString();
                                break;
                            case 5:
                                cb_5.Enabled = true;
                                parm_5.Enabled = true;
                                ddReturnType_5.Enabled = true;

                                cb_5.Checked = true;
                                hf_5.Value = dtp.Rows[i]["ID"].ToString();
                                parm_5.Text = dtp.Rows[i]["ParameterName"].ToString();
                                ddReturnType_5.SelectedValue = dtp.Rows[i]["ParameterType"].ToString();
                                break;
                        }
                    }
                }
            }
        }

        protected void btnReset_Click(object sender, EventArgs e)
        {
            resetForm();
        }

        private void resetForm()
        {

            btnSave.Visible = false;
            ViewState["formulaIDToUpdate"] = 0;

            txtFurmulaName.Text = "";
            ddReturnType.SelectedValue = "number";
            txtThenPart.Text = "";
            txtWhenPart.Text = "";

            lblThenExp.Text = "";
            lblWhenExp.Text = "";

            parm_1.Text = "";
            parm_2.Text = "";
            parm_3.Text = "";
            parm_4.Text = "";
            parm_5.Text = "";

            ddReturnType_1.SelectedValue = "number";
            ddReturnType_2.SelectedValue = "number";
            ddReturnType_3.SelectedValue = "number";
            ddReturnType_4.SelectedValue = "number";
            ddReturnType_5.SelectedValue = "number";

            cb_1.Checked = false;
            cb_2.Checked = false;
            cb_3.Checked = false;
            cb_4.Checked = false;
            cb_5.Checked = false;

            parm_1.Enabled = false;
            parm_2.Enabled = false;
            parm_3.Enabled = false;
            parm_4.Enabled = false;
            parm_5.Enabled = false;

            ddReturnType_1.Enabled = false;
            ddReturnType_2.Enabled = false;
            ddReturnType_3.Enabled = false;
            ddReturnType_4.Enabled = false;
            ddReturnType_5.Enabled = false;
        }

        private void resetForUpdate()
        {
            parm_1.Text = "";
            parm_2.Text = "";
            parm_3.Text = "";
            parm_4.Text = "";
            parm_5.Text = "";

            ddReturnType_1.SelectedValue = "number";
            ddReturnType_2.SelectedValue = "number";
            ddReturnType_3.SelectedValue = "number";
            ddReturnType_4.SelectedValue = "number";
            ddReturnType_5.SelectedValue = "number";

            cb_1.Checked = false;
            cb_2.Checked = false;
            cb_3.Checked = false;
            cb_4.Checked = false;
            cb_5.Checked = false;

            lblThenExp.Text = "";
            lblWhenExp.Text = "";
        }
    }
}